﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using ExaminationSys.DataAccess.Entity;
using SQLinq;
using Dapper;
using SQLinq.Dapper;

using DapperExtensions;

namespace ExaminationSys.DataAccess.Repository
{
    public class StudentRankRepository : Repository<StudentRank>
    {
        public StudentRankRepository(SqlConnection context)
            : base(context)
        {
            db = context;
        }

        private SqlConnection db;

        public override StudentRank GetById(dynamic id)
        {
            return base.ObjectSet.Get<StudentRank>(db, id, null, null);
        }
    }

    public class StudentRankService
    {
        private StudentRankService()
        {
        }

        public StudentRank Get(Guid id)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var result = work.StudentRank.GetById(id);
                return result;

            }
        }

        // 说明：type=1（勤奋榜），type=2（状元榜）

        public StudentRank GetDiligentRankBStudentId(string studentId)
        {
            using (var db = DataContent.DBServer())
            {
                var sql =
                    string.Format("SELECT *FROM StudentRank sr  WHERE sr.StudentID='{0}' AND sr.[Status]=1 and type=1",
                                  studentId);
                var result =
                    db.Query<StudentRank>(sql).FirstOrDefault();
                return result;
            }
        }

        public StudentRank GetChampionRankBStudentId(string studentId)
         {
             using (var db = DataContent.DBServer())
             {
                 var sql =
                     string.Format("SELECT *FROM StudentRank sr  WHERE sr.StudentID='{0}' AND sr.[Status]=1 and type=2",
                                   studentId);
                 var result =
                     db.Query<StudentRank>(sql).FirstOrDefault();
                 return result;
             }
         }

        public List<StudentRank> GetDiligentRankList()
        {
            using (var db = DataContent.DBServer())
            {
                var result =
                    db.Query<StudentRank>(@"SELECT top 6 sr.*,s.UserName FROM StudentRank sr INNER JOIN Student s
                            ON sr.StudentID=s.ID WHERE sr.[Type]=1 and  sr.[Status]=1 order BY sr.[Rank]").
                        ToList();
                return result;
            }
        }
        public List<StudentRank> GetChampionRankList()
        {
            using (var db = DataContent.DBServer())
            {
                var result =
                    db.Query<StudentRank>(@"SELECT top 4 sr.*,s.UserName FROM StudentRank sr INNER JOIN Student s
                                ON sr.StudentID=s.ID WHERE sr.[Type]=2 and  sr.[Status]=1 order BY sr.[Rank]").
                        ToList();
                return result;
            }
        }
    }
}
